package com.hotelManager.www.dao;
import com.hotelManager.www.po.BrowseHistory;
import com.hotelManager.www.po.Shop;
import com.hotelManager.www.po.ShopBlacklist;
import com.hotelManager.www.po.SignInRecord;
import com.hotelManager.www.po.User;
import com.hotelManager.www.util.JDBCUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;
import java.math.BigDecimal;

public class UserDao {


    //------------用户注册----------
    public boolean register(User user, Connection conn) throws SQLException {
        // 依赖：org.springframework.security.crypto.bcrypt.BCryptPasswordEncoder

        String sql = "INSERT INTO users(username, password, role, balance, recharge_status, recharge_amount) VALUES (?, ?, ?, ?, ?, ?)";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, user.getUsername());
            ps.setString(2, user.getPassword());
            ps.setString(3, user.getRole());
            ps.setBigDecimal(4, user.getBalance());
            ps.setString(5, user.getRechargeStatus());
            ps.setBigDecimal(6, user.getRechargeAmount());
            return ps.executeUpdate() > 0;
        }
    }
    //------------用户登录----------
    public User login(Connection conn, String username, String password) throws SQLException {
        String sql = "SELECT * FROM users WHERE username = ? AND password = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, username);
            ps.setString(2, password);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    User user = new User();
                    user.setId(rs.getInt("id"));
                    user.setUsername(rs.getString("username"));
                    user.setPassword(rs.getString("password"));
                    user.setRole(rs.getString("role"));
                    user.setBalance(rs.getBigDecimal("balance"));
                    user.setRechargeStatus(rs.getString("recharge_status"));
                    user.setRechargeAmount(rs.getBigDecimal("recharge_amount"));
                    return user;
                }
            }
        }
        return null;
    }
    // ------------用户信息更新----------
    public boolean updateUser(User user, Connection conn) throws SQLException {
        String sql = "UPDATE users SET password = ?, role = ?, balance = ?, recharge_status = ?, recharge_amount = ? WHERE id = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, user.getPassword());
            ps.setString(2, user.getRole());
            ps.setBigDecimal(3, user.getBalance());
            ps.setString(4, user.getRechargeStatus());
            ps.setBigDecimal(5, user.getRechargeAmount());
            ps.setInt(6, user.getId());
            return ps.executeUpdate() > 0;
        }
    }
    // ------------用户信息查询----------
    public User getUserByUsername(Connection conn, String username) throws SQLException {
        String sql = "SELECT * FROM users WHERE username = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, username);
            try (ResultSet rs = ps.executeQuery()) {
                if (rs.next()) {
                    User user = new User();
                    user.setId(rs.getInt("id"));
                    user.setUsername(rs.getString("username"));
                    user.setPassword(rs.getString("password"));
                    user.setBalance(rs.getBigDecimal("balance"));
                    user.setRole(rs.getString("role"));
                    user.setRechargeStatus(rs.getString("recharge_status"));
                    user.setRechargeAmount(rs.getBigDecimal("recharge_amount"));
                    return user;
                }
            }
        }
        return null;
    }

    // 根据充值状态查询用户
    public List<User> findByRechargeStatus(Connection conn, String rechargeStatus) throws SQLException {
        String sql = "SELECT * FROM users WHERE recharge_status = ?";
        List<User> users = new ArrayList<>();
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, rechargeStatus);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    users.add(extractUserFromResultSet(rs));
                }
            }
        }
        return users;
    }

    // 更新用户信息
    public boolean update(Connection conn, User user) throws SQLException {

        String sql = "UPDATE users SET username = ?, password = ?, role = ?, balance = ?, " +
                    "recharge_status = ?, recharge_amount = ?, status = COALESCE(?, 'active') WHERE id = ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, user.getUsername());
            stmt.setString(2, user.getPassword());
            stmt.setString(3, user.getRole());
            stmt.setBigDecimal(4, user.getBalance());
            stmt.setString(5, user.getRechargeStatus());
            stmt.setBigDecimal(6, user.getRechargeAmount());
            stmt.setString(7, user.getStatus());
            stmt.setInt(8, user.getId());
            return stmt.executeUpdate() > 0;
        }
    }


    //更新可以修改的信息
    public boolean updateUserPasswordOrUsername(Connection conn, User user) throws SQLException {
        System.out.println("[UserDao.updateUserPasswordOrUsername] 开始执行SQL更新 - 用户ID: " + user.getId());
        String sql = "UPDATE users SET username = ?, password = ? WHERE id = ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setString(1, user.getUsername());
            stmt.setString(2, user.getPassword());
            stmt.setInt(3, user.getId());
            int result = stmt.executeUpdate();
            System.out.println("[UserDao.updateUserPasswordOrUsername] SQL执行结果: " + (result > 0 ? "成功" : "失败") + ", 影响行数: " + result);
            return result > 0;
        } catch (SQLException e) {
            System.out.println("[UserDao.updateUserPasswordOrUsername] SQL执行异常: " + e.getMessage());
            throw e;
        }
    }

    // 从ResultSet中提取User对象
    private User extractUserFromResultSet(ResultSet rs) throws SQLException {
        User user = new User();  // 使用无参构造函数
        user.setId(rs.getInt("id"));
        user.setUsername(rs.getString("username"));
        user.setPassword(rs.getString("password"));
        user.setRole(rs.getString("role"));
        user.setBalance(rs.getBigDecimal("balance"));
        user.setRechargeStatus(rs.getString("recharge_status"));
        user.setRechargeAmount(rs.getBigDecimal("recharge_amount"));
        return user;
    }

    public User findById(Integer id) {
        String sql = "SELECT * FROM users WHERE id = ?";
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            Connection conn = JDBCUtils.getConnection();
            ps = conn.prepareStatement(sql);
            ps.setInt(1, id);
            rs = ps.executeQuery();
            if (rs.next()) {
                return extractUserFromResultSet(rs);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return null;
    }

    // 更新用户余额
    public boolean updateBalance(Integer userId, BigDecimal amount) throws Exception {
        String sql = "UPDATE users SET balance = balance + ? WHERE id = ? AND balance + ? >= 0";
        try (
                Connection conn = JDBCUtils.getConnection();
                PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setBigDecimal(1, amount);
            ps.setInt(2, userId);
            ps.setBigDecimal(3, amount);
            return ps.executeUpdate() > 0;
        }
    }

    // 更新充值状态
    public boolean updateRechargeStatus(Integer userId, String status, BigDecimal amount, Connection conn) throws SQLException {
        String sql = "UPDATE users SET recharge_status = ?, recharge_amount = ? WHERE id = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setString(1, status);
            ps.setBigDecimal(2, amount);
            ps.setInt(3, userId);
            return ps.executeUpdate() > 0;
        }
    }

    // 获取所有待审核的充值请求
    public List<User> getPendingRechargeRequests(Connection conn) throws SQLException {
        String sql = "SELECT * FROM users WHERE recharge_status = 'pending'";
        List<User> users = new ArrayList<>();
        try (PreparedStatement ps = conn.prepareStatement(sql);
             ResultSet rs = ps.executeQuery()) {
            while (rs.next()) {
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setPassword(rs.getString("password"));
                user.setBalance(rs.getBigDecimal("balance"));
                user.setRole(rs.getString("role"));
                user.setRechargeStatus(rs.getString("recharge_status"));
                user.setRechargeAmount(rs.getBigDecimal("recharge_amount"));
                users.add(user);
            }
        }
        return users;
    }

    // 添加签到记录
    public boolean addSignInRecord(Connection conn, SignInRecord record) throws SQLException {
        String sql = "INSERT INTO sign_in_records (user_id, sign_in_date, points_earned) VALUES (?, ?, ?)";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, record.getUserId());
            stmt.setDate(2, record.getSignInDate());
            stmt.setInt(3, record.getPointsEarned());
            return stmt.executeUpdate() > 0;
        }
    }

    // 更新用户签到状态
    public boolean updateUserSignInStatus(Connection conn, int userId, int additionalPoints) throws SQLException {
        String sql = "UPDATE user SET sign_in_days = sign_in_days + 1, last_sign_in_date = ?, points = points + ? WHERE id = ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setDate(1, new java.sql.Date(System.currentTimeMillis()));
            stmt.setInt(2, additionalPoints);
            stmt.setInt(3, userId);
            return stmt.executeUpdate() > 0;
        }
    }

    // 检查用户是否已签到
    public boolean hasUserSignedInToday(Connection conn, int userId) throws SQLException {
        String sql = "SELECT last_sign_in_date FROM user WHERE id = ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    java.sql.Date lastSignInDate = rs.getDate("last_sign_in_date");
                    return lastSignInDate != null && lastSignInDate.equals(new java.sql.Date(System.currentTimeMillis()));
                }
            }
        }
        return false;
    }

    // 查询用户的浏览历史
    public List<Shop> getBrowseHistory(Connection conn, int userId) throws SQLException {
        String sql = "SELECT s.* FROM browse_history bh " +
                    "JOIN shop s ON bh.shop_id = s.id " +
                    "WHERE bh.user_id = ? " +
                    "AND NOT EXISTS (SELECT 1 FROM shop_blacklist bl WHERE bl.user_id = ? AND bl.shop_id = s.id) " +
                    "ORDER BY bh.browse_time DESC";
        List<Shop> browseHistory = new ArrayList<>();
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            stmt.setInt(2, userId);
            try (ResultSet rs = stmt.executeQuery()) {
                ShopDao shopDao = new ShopDao();
                while (rs.next()) {
                    browseHistory.add(shopDao.extractShopFromResultSet(rs));
                }
            }
        }
        return browseHistory;
    }

    // 添加浏览历史记录
    public boolean addBrowseHistory(Connection conn, int userId, int shopId) throws SQLException {
        String sql = "INSERT INTO browse_history (user_id, shop_id, browse_time) VALUES (?, ?, ?)";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            stmt.setInt(2, shopId);
            stmt.setTimestamp(3, new Timestamp(System.currentTimeMillis()));
            return stmt.executeUpdate() > 0;
        }
    }

    // 查询用户的浏览历史
    public List<BrowseHistory> getBrowseHistoryByUserId(Connection conn, int userId) throws SQLException {
        String sql = "SELECT * FROM browse_history WHERE user_id = ? ORDER BY browse_time DESC";
        List<BrowseHistory> historyList = new ArrayList<>();
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    BrowseHistory history = new BrowseHistory();
                    history.setId(rs.getInt("id"));
                    history.setUserId(rs.getInt("user_id"));
                    history.setShopId(rs.getInt("shop_id"));
                    history.setBrowseTime(rs.getTimestamp("browse_time"));
                    historyList.add(history);
                }
            }
        }
        return historyList;
    }
    // 从黑名单中移除商家
    public boolean removeFromBlacklist(Connection conn, int userId, int shopId) throws SQLException {
        String sql = "DELETE FROM shop_blacklist WHERE user_id = ? AND shop_id = ?";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            stmt.setInt(2, shopId);
            return stmt.executeUpdate() > 0;
        }
    }
    

    // 添加商家到黑名单
    public boolean addToBlacklist(Connection conn, int userId, int shopId) throws SQLException {
        String sql = "INSERT INTO shop_blacklist (user_id, shop_id) VALUES (?, ?)";
        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            stmt.setInt(2, shopId);
            return stmt.executeUpdate() > 0;
        }
    }


    public boolean isShopBlacklisted(Connection conn, int userId, int shopId) throws SQLException {
        String sql = "SELECT 1 FROM shop_blacklist WHERE user_id = ? AND shop_id = ?";
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            ps.setInt(2, shopId);
            try (ResultSet rs = ps.executeQuery()) {
                return rs.next();
            }
        }
    }

    public List<Shop> getUserBlacklist(Connection conn, int userId) throws SQLException {
        String sql = "SELECT s.shopname, s.contact, s.address " +
                "FROM shop_blacklist sb " +
                "JOIN shop s ON sb.shop_id = s.id " +
                "WHERE sb.user_id = ?";
        List<Shop> shopList = new ArrayList<>();
        try (PreparedStatement ps = conn.prepareStatement(sql)) {
            ps.setInt(1, userId);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    Shop shop = new Shop();
                    shop.setShopname(rs.getString("shopname"));
                    shop.setContact(rs.getString("contact"));
                    shop.setAddress(rs.getString("address"));
                    shopList.add(shop);
                }
            }
        }
        return shopList;
    }
}
